using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Sql;
using System.Data.SqlClient;
using System.Data.Common;
using System.Data.SqlServerCe;
using System.IO;

namespace Faust.Andrew.MTG.Migration
{
    class SqlExpressToCE
    {
        public void Migrate(string sourceSQLExpress, string destCE)
        {
            using (System.Data.SqlClient.SqlConnection sqlConnection = new SqlConnection(sourceSQLExpress))
            {
                sqlConnection.Open();

                using (System.Data.SqlServerCe.SqlCeConnection ceConnection = new SqlCeConnection(GetCeConnectionString(destCE)))
                {
                    ceConnection.Open();

                    ConvertCards(sqlConnection, ceConnection);
                }
            }
        }

        private void ConvertCards(SqlConnection sqlConnection, SqlCeConnection ceConnection)
        {
            using (SqlCommand sourceCommand = new SqlCommand())
            {
                sourceCommand.Connection = sqlConnection;
                sourceCommand.CommandText = "select * from Cards";

                using (SqlDataReader reader = sourceCommand.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        using (SqlCeCommand command = new SqlCeCommand())
                        {
                            command.Connection = ceConnection;
                            command.CommandText = "insert into cards ([MultiverseID], [Rarity], [Toughness], [ToughnessNumeric], [Power], [PowerNumeric], "
                                + "[CardTypes], [ConvertedManaCost], [ConvertedManaCostNumeric], [Name], [Expansion], [SubType], [CardText], [ManaCost], [Quantity], [Color], [Image]) values ("
                                + "@MultiverseID, @Rarity, @Toughness, @ToughnessNumeric, @Power, @PowerNumeric, "
                                + "@CardTypes, @ConvertedManaCost, @ConvertedManaCostNumeric, @Name, @Expansion, @SubType, @CardText, @ManaCost, @Quantity, @Color, @Image)";

                            for (int i = 0; i < reader.FieldCount; i++)
                            {
                                command.Parameters.AddWithValue(String.Format("@{0}", reader.GetName(i)), reader[i]);
                            }

                            command.ExecuteNonQuery();
                        }
                    }
                }
            }
        }

        private string GetCeConnectionString(string cePath)
        {
            return "Data Source=" + cePath + ";Max Database Size=4000";
        }
    }
}
